﻿using NStandard;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using System.Collections;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Reflection;



/// <summary>
/// 导入导出帮助类
/// </summary>
public class ExcelUtils
{
    #region 导入
    /*
   [HttpPost]
  public async Task<IResultDTO> ExcelIn([FromForm]ExcelInInput req)
  {

      using (var excelPackage = new ExcelPackage(req.file.OpenReadStream()))
      {
          try
          {
              var list = ExcelUtils.GetExcelData<CountySageExcelInInput>(excelPackage.Workbook.Worksheets[0], 2);
              return await _countySageService.SaveExcel(list);
          }
          catch (Exception ex)
          {
              return ResultDTO.Error(ex.Message);
          }
      }
  }
   */
    /// <summary>
    /// 获取导入表格的值
    ///   
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="worksheet">表格</param>
    /// <param name="startRowIndex">数据超始行,标题行一般为1</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static List<T> GetExcelData<T>(ExcelWorksheet worksheet, int startRowIndex) where T : class
    {
        int rowCount = worksheet.Dimension.Rows;
        int ColCount = worksheet.Dimension.Columns;

        List<T> list = new List<T>();

        Dictionary<string, int> dicts = new Dictionary<string, int>();

        for (int row = startRowIndex; row <= rowCount; row++)
        {

            if (row == startRowIndex)
            {
                List<string> colnames = new List<string>();
                for (int col = 1; col <= ColCount; col++)
                {
                    colnames.Add(worksheet.Cells[row, col].Text);
                }
                try
                {
                    dicts = GetProps<T>(colnames, row);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else
            {
                var item = CreateRow<T>(worksheet.Cells, dicts, row);
                list.Add(item);
            }
        }
        return list;
    }


    private static T CreateRow<T>(ExcelRange cells, Dictionary<string, int> dicts, int row) where T : class
    {
        T t = Activator.CreateInstance(typeof(T)) as T;

        foreach (var item in t.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
        {

            if (!item.CanWrite) //如果能设置值
            {
                continue;
            }
            if (!dicts.ContainsKey(item.Name))
            {
                continue;
            }
            var v = cells[row, dicts[item.Name]].Text ?? "";
            var ty = item.PropertyType.GetUnderlyingTypeIfNullable();
            if (ty == typeof(DateTime))
            {
                item.SetValue(t, v.TryDateTime());
            }
            else if (ty == typeof(DateOnly))
            {
                item.SetValue(t, v.TryDateOnly());
            }
            else if (ty == typeof(int))
            {
                item.SetValue(t, v.TryInt());
            }
            else if (ty == typeof(long))
            {
                item.SetValue(t, v.TryLong());
            }
            else if (ty == typeof(decimal))
            {
                item.SetValue(t, v.TryDecimal());
            }
            else if (ty == typeof(UInt16))
            {
                item.SetValue(t, v.TryUInt16());
            }
            else if (ty == typeof(string))
            {
                item.SetValue(t, v);
            }
            else if (ty.IsEnum)
            {
                //

                Array enumValues = Enum.GetValues(ty);

                foreach (Enum value in enumValues)
                {
                    FieldInfo? field = ty.GetField(value.ToString());

                    if (field != null)
                    {
                        // 获取枚举值对应的属性
                        DescriptionAttribute[] attributes = field.GetCustomAttributes(typeof(DescriptionAttribute), false) as DescriptionAttribute[];

                        if (attributes != null && attributes.Length > 0)
                        {
                            var description = attributes[0].Description;
                            if (v == description)
                            {
                                item.SetValue(t, value);
                                break;
                            }
                        }

                        DisplayAttribute[] displayAttributes = field.GetCustomAttributes(typeof(DisplayAttribute), false) as DisplayAttribute[];
                        if (displayAttributes != null && displayAttributes.Length > 0)
                        {

                            var description = displayAttributes[0].Name;
                            if (v == description)
                            {
                                item.SetValue(t, value);
                                break;
                            }
                        }
                    }


                }
                //item.SetValue(t, v);
            }
            else if (ty == typeof(bool))
            {
                bool? vv = v == "是" ? true : v == "否" ? false : null;
                if (!item.PropertyType.IsNullable())
                {
                    if (vv == null) vv = false;
                }
                item.SetValue(t, vv);
            }
            else if (ty.IsPrimitive)
            {
                try
                {
                    var vv = Convert.ChangeType(v.ToString(), ty);
                    item.SetValue(t, vv);
                }
                catch
                {

                }
            }
            else
            {
                throw new NotSupportedException($"不支持类型{item.Name}");
                //Console.Write(item.Name);
            }
        }
        return t;
    }


    #endregion
    #region 导出
    /*
    /// <summary>
    /// 导出库存表明细
    /// </summary>
    /// <param name="req"></param>
    /// <returns></returns>
    [HttpPost]
    public async Task<IActionResult> ExportExcel(FilterExportInput<StockInFilterInput> req)
    {
        var obj = await _stockInService.GetListAsync(req);
        var memoryStream = new MemoryStream();
        ExcelUtils.DynamicExportList(obj.Data?.List, req.Columns, memoryStream, "库存表明细", req.ShowSeq, req.Min, req.ShowTitle, req.Title);
        memoryStream.Seek(0, SeekOrigin.Begin);
        var fileName = $"{req.FileName}";
        Response.Headers.Append("Access-Control-Expose-Headers", "Content-Disposition");
        return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
    }

    /// <summary>
    /// 获取流向导出，一次导三张表
    /// </summary>
    /// <param name="req"></param>
    /// <returns></returns>
    [HttpPost]
    public async Task<IActionResult> ExportForExcel(FilterExportInput<WorkSaleFilterRequest?> req)
    {
        if (req.Filter == null)
        {
            throw new Exception("必须输入协议编码");
        }
        if (req.Filter.xybm.IsNull())
        {
            throw new Exception("必须输入协议编码");
        }
        var begTime = req?.Filter?.Date?.Count == 2 ? (req?.Filter?.Date[0] ?? DateTime.Now) : DateTime.Now;
        var endTime = req?.Filter?.Date?.Count == 2 ? (req?.Filter?.Date[1] ?? DateTime.Now) : DateTime.Now;

        var obj = await _workSaleService.GetListAsync(new RequestPageEntity<WorkSaleFilterRequest>
        {
            CurrentPage = req.CurrentPage,
            // DynamicFilter = req.DynamicFilter,
            Filter = new WorkSaleFilterRequest
            {
                Date = req.Filter.Date,
                Key = req.Filter.Key,
                xybm = req.Filter.xybm,
                xycj = req.Filter.xycj,
                state = 1,
                isDeleted = false,
            },
            //  IsAsc = req.IsAsc,
            PageSize = req.PageSize,
        });


        var obj1 = await _WorkPurinService.GetListAsync(new RequestPageEntity<WorkPurinFilterRequest>
        {
            CurrentPage = req.CurrentPage,
            // DynamicFilter = req.DynamicFilter,
            Filter = new WorkPurinFilterRequest
            {
                Date = req.Filter.Date,
                Key = req.Filter.Key,
                xybm = req.Filter.xybm,
                xycj = req.Filter.xycj,
                state = 1,
                IsDeleted = false,

            },
            //  IsAsc = req.IsAsc,
            PageSize = req.PageSize,
            //  Sort = req.Sort,
        });



        var obj2 = await _storeService.GetListAsync(new RequestPageEntity<StoreFilterRequest>
        {
            PageSize = req.PageSize,
            CurrentPage = 1,
            Filter = new StoreFilterRequest
            {
                xycj = req.Filter.xycj,
                xybm = req.Filter.xybm,
                entType = 1,
                state = 1,
                isDeleted = false,
                Date = new DateTime(endTime.Year, endTime.Month, 1).AddDays(-1).ToString("yyyy-MM-dd"),
            }
        });

        var memoryStream = new MemoryStream();
        DynamicExportList<WorkSaleForListResponse, WorkPurinForListResponse, StoreForListResponse>(memoryStream,
            new ExcelOutParam<WorkSaleForListResponse>
            {
                Columns = new List<Column>() {
            new Column{  Label="日期", Prop=nameof(WorkSaleForListResponse.DATES), },
             new Column{  Label="编码", Prop=nameof(WorkSaleForListResponse.GOODSCODE), },
             new Column{  Label="商品名", Prop=nameof(WorkSaleForListResponse.GOODSNAME), },
             new Column{  Label="规格", Prop=nameof(WorkSaleForListResponse.GOODSSPEC), },
             new Column{  Label="生产厂家", Prop=nameof(WorkSaleForListResponse.MANUFACTURER), },
             new Column{  Label="批号", Prop=nameof(WorkSaleForListResponse.BATCHCODE), },
             new Column{  Label="效期", Prop=nameof(WorkSaleForListResponse.VALDATE), },
             new Column{  Label="店铺编码", Prop=nameof(WorkSaleForListResponse.OrgCode), },
             new Column{  Label="店铺", Prop=nameof(WorkSaleForListResponse.REQORGNAME), },
             new Column{  Label="店铺全称", Prop=nameof(WorkSaleForListResponse.FULLNAME), },
             new Column{  Label="客户编码", Prop=nameof(WorkSaleForListResponse.BusinessCode), },
             new Column{  Label="客户名称", Prop=nameof(WorkSaleForListResponse.BusinessName), },
             new Column{  Label="数量", Prop=nameof(WorkSaleForListResponse.NUM), },
             new Column{  Label="单价", Prop=nameof(WorkSaleForListResponse.TAXPRICE), },
             new Column{  Label="金额", Prop=nameof(WorkSaleForListResponse.TAXAMOUNT), },
             new Column{  Label="单位", Prop=nameof(WorkSaleForListResponse.UNIT), },
             new Column{  Label="销售员", Prop=nameof(WorkSaleForListResponse.SALENAME), },
             new Column{  Label="协议编码", Prop=nameof(WorkSaleForListResponse.XYBM), },
             new Column{  Label="协议厂家", Prop=nameof(WorkSaleForListResponse.XYCJ), },
        }, Items= obj.Data?.List, SheetName= "销售表", ShowSeq=false, ShowTitle=false, Title= "销售表"
            }
        , new ExcelOutParam<WorkPurinForListResponse> { }, new ExcelOutParam<StoreForListResponse> { });

        memoryStream.Seek(0, SeekOrigin.Begin);
        var fileName = $"融合流向表";
        Response.Headers.Append("Access-Control-Expose-Headers", "Content-Disposition");
        return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
    }

     */



    /// <summary>
    /// 多表导出
    /// </summary>
    /// <typeparam name="T1"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <param name="outStream"></param>
    /// <param name="p1"></param>
    /// <param name="p2"></param>
    public static void DynamicExportList<T1, T2>(Stream outStream, ExcelOutParam<T1> p1, ExcelOutParam<T2> p2)
    {
        using var excelPackage = new ExcelPackage(outStream);
        DynamicExportList(outStream, p1);
        DynamicExportList(outStream, p2);
        excelPackage.Save();
    }

    /// <summary>
    /// 多表导出
    /// </summary>
    /// <typeparam name="T1"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <typeparam name="T3"></typeparam>
    /// <param name="outStream"></param>
    /// <param name="p1"></param>
    /// <param name="p2"></param>
    /// <param name="p3"></param>
    public static void DynamicExportList<T1, T2, T3>(Stream outStream, ExcelOutParam<T1> p1, ExcelOutParam<T2> p2, ExcelOutParam<T3> p3)
    {
        using var excelPackage = new ExcelPackage(outStream);
        DynamicExportList(outStream, p1);
        DynamicExportList(outStream, p2);
        DynamicExportList(outStream, p3);
        excelPackage.Save();
    }
    /// <summary>
    /// 多表导出
    /// </summary>
    /// <typeparam name="T1"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <typeparam name="T3"></typeparam>
    /// <typeparam name="T4"></typeparam>
    /// <param name="outStream"></param>
    /// <param name="p1"></param>
    /// <param name="p2"></param>
    /// <param name="p3"></param>
    /// <param name="p4"></param>
    public static void DynamicExportList<T1, T2, T3, T4>(Stream outStream, ExcelOutParam<T1> p1, ExcelOutParam<T2> p2, ExcelOutParam<T3> p3, ExcelOutParam<T4> p4)
    {
        using var excelPackage = new ExcelPackage(outStream);
        DynamicExportList(outStream, p1);
        DynamicExportList(outStream, p2);
        DynamicExportList(outStream, p3);
        DynamicExportList(outStream, p4);
        excelPackage.Save();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExcelOutParam<T>
    {
        /// <summary>
        /// 数据
        /// </summary>
        public IList<T> Items { get; set; } = new List<T>();

        /// <summary>
        /// 列信息
        /// </summary>
        public IList<Column>? Columns { get; set; }

        /// <summary>
        /// sheetName
        /// </summary>
        public string SheetName { get; set; } = "Test";

        /// <summary>
        /// 显示序号列
        /// </summary>
        public bool? ShowSeq { get; set; } = false;

        /// <summary>
        /// 开始序号
        /// </summary>
        public int? StartSeq { get; set; } = 1;


        /// <summary>
        /// 是否显示表的标题
        /// </summary>
        public bool? ShowTitle { get; set; } = false;

        /// <summary>
        /// 表的标题
        /// </summary>
        public string? Title { get; set; } = "";
    }

    /// <summary>
    /// 单表导出
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="outStream"></param>
    /// <param name="para"></param>
    public static void DynamicExportList<T>(Stream outStream, ExcelOutParam<T> para)
    {
        DynamicExportList(para.Items, para.Columns, outStream, para.SheetName, para.ShowSeq, para.StartSeq, para.ShowTitle, para.Title);
    }

    /// <summary>
    /// 单表导出
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <param name="colus"></param>
    /// <param name="outStream"></param>
    /// <param name="sheetName"></param>
    /// <param name="showSeq"></param>
    /// <param name="startSeq"></param>
    /// <param name="showTitle"></param>
    /// <param name="title"></param>
    public static void DynamicExportList<T>(IList<T>? list, IList<Column>? colus, Stream outStream, string sheetName, bool? showSeq = false, int? startSeq = 1, bool? showTitle = false, string? title = "")
    {

        List<Column> columns = new List<Column>();
        //多级表头处理
        if (colus != null)
        {
            AddCols(colus);
            void AddCols(IList<Column> cs)
            {
                if (cs != null)
                    foreach (var item in cs)
                    {
                        if (item.Columns == null)
                        {
                            columns.Add(item);
                        }

                        else AddCols(item.Columns);
                    }
            }
        }
        int startRow = showTitle.GetValueOrDefault() ? 2 : 1;

        using (var excelPackage = new ExcelPackage(outStream))
        {
            if (string.IsNullOrWhiteSpace(sheetName)) sheetName = "Sheet1";
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add(sheetName);




            if (columns == null || columns.Count <= 0)
            {
                worksheet.Cells[startRow + 1, 1].Value = "没有导出的列!";
            }
            if (showTitle.GetValueOrDefault())
            {
                //
                worksheet.Cells[1, 1].Value = title;
                if (showSeq.GetValueOrDefault())
                {
                    worksheet.Cells[1, 1, 1, columns.Count + 1].Merge = true;
                    worksheet.Cells[1, 1, 1, columns.Count + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                else
                {
                    worksheet.Cells[1, 1, 1, columns.Count].Merge = true;
                    worksheet.Cells[1, 1, 1, columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }


            // PropertyInfo[] props = null;
            if (list != null && list.Count > 0)
            {

                using (FillTable fillTable = new FillTable(list[0], worksheet, columns ?? new List<Column>(), startRow, showSeq, startSeq ?? 1))
                {
                    fillTable.SetTable(list);
                }

            }
            else
            {
                worksheet.Cells[startRow + startRow, 1].Value = "暂无数据~";
            }

            excelPackage.Save();


        }
    }

    /// <summary>
    /// 
    /// </summary>
    public class FillTable : IDisposable
    {
        Dictionary<Column, PropertyInfo?> pros = new Dictionary<Column, PropertyInfo?>();
        List<Column> _columns = new List<Column>();
        ExcelWorksheet worksheet;
        bool? showSeq;
        int startSeq = 1;
        HttpClient httpClient;
        int startRow;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <param name="worksheet"></param>
        /// <param name="columns"></param>
        /// <param name="showSeq"></param>
        /// <param name="startRow"></param>
        /// <param name="startSeq">开始的序号</param>
        public FillTable(object o, ExcelWorksheet worksheet, List<Column> columns, int startRow, bool? showSeq, int startSeq = 1)
        {
            if (o is null)
            {
                throw new ArgumentNullException(nameof(o));
            }

            this.startRow = startRow;
            _columns = columns;
            this.worksheet = worksheet;
            this.showSeq = showSeq;
            this.startSeq = startSeq;
            MapPros(o);
            SetHeader(startRow);
            httpClient = new HttpClient();
        }
        private void MapPros(object o)
        {
            var ps = o.GetType().GetProperties();
            foreach (var item in _columns)
            {
                if (item.Prop.IndexOf(".") != -1)
                {
                    var props = item.Prop.Split(".", StringSplitOptions.RemoveEmptyEntries);
                    if (props != null)
                    {
                        List<string> proNames = new List<string>();
                        var p = GetPro(o.GetType(), props, 0, ref proNames);
                        if (p != null)
                        {
                            item.Prop = string.Join(".", proNames);
                        }
                        pros.Add(item, p);
                    }
                    else pros.Add(item, null);
                }
                else
                {
                    var p = ps.FirstOrDefault(x => x.Name.ToLower() == item.Prop.ToLower());
                    if (p != null)
                    {
                        item.Prop = p.Name;
                    }
                    pros.Add(item, p);
                }
            }
            // Console.WriteLine($"{JSON.Serialize(_columns)}");
        }
        /// <summary>
        /// 递归获取属性
        /// </summary>
        /// <returns></returns>
        private PropertyInfo? GetPro(Type t, string[] props, int pos, ref List<string> proNames)
        {


            string proName = props[pos];
            //如果是 继续
            var p = t.GetProperties().FirstOrDefault(x => x.Name.ToLower() == proName.ToLower());

            if (p == null) { return null; }
            if (p.PropertyType.IsClass && !typeof(IEnumerable).IsAssignableFrom(p.PropertyType))
            {
                pos++;
                if (pos < props.Length)
                {
                    proNames.Add(p.Name);
                    return GetPro(p.PropertyType, props, pos, ref proNames);
                }

            }
            proNames.Add(p.Name);
            return p;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void SetTable<T>(IList<T>? list)
        {

            if (list == null) return;

            var row = startRow + 0;
            foreach (var item in list)
            {
                row++;
                SetRow(item, row, row - startRow);
            }
            SetColStyle();
        }

        private void SetColStyle()
        {
            var i = showSeq.GetValueOrDefault() ? 1 : 0;
            if (showSeq.GetValueOrDefault())
            {
                worksheet.Columns[i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Columns[i].AutoFit();
            }
            foreach (var column in _columns)
            {
                i++;
                if (column.Align.NotNull())
                {
                    if (column.Align == "left")
                    {
                        worksheet.Columns[i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                    else if (column.Align == "center")
                    {
                        worksheet.Columns[i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }
                    else if (column.Align == "right")
                    {
                        worksheet.Columns[i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }
                }

                #region 设置列宽


                if (column.Width is string w)
                {
                    double.TryParse(w.Replace("px", ""), out double ww);
                    worksheet.Columns[i].Width = ww / 8;
                }
                else if (column.Width is float w1)
                {
                    worksheet.Columns[i].Width = w1 / 8;
                }
                else
                    worksheet.Columns[i].AutoFit();


                #endregion
            }

        }

        private void SetHeader(int startRow)
        {
            var col = showSeq.GetValueOrDefault() ? 1 : 0;
            var row = startRow + 0;
            if (showSeq.GetValueOrDefault())
            {
                worksheet.Cells[row, col].Value = "序号";
            }

            foreach (var item in pros)
            {
                col++;
                worksheet.Cells[row, col].Value = item.Key.Label;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="row"></param>
        /// <param name="index"></param>
        private void SetRow(object obj, int row, int index)
        {
            var col = showSeq.GetValueOrDefault() ? 1 : 0;
            if (showSeq.GetValueOrDefault())
            {
                worksheet.Cells[row, col].Value = index + this.startSeq - 1;
            }
            foreach (var item in pros)
            {
                col++;
                SetValue(worksheet.Cells[row, col], obj, item);
            }

        }

        private void SetValue(ExcelRange range, object? obj, KeyValuePair<Column, PropertyInfo?> item)
        {
            if (obj != null)
            {
                if (item.Key.Prop.IndexOf(".") != -1)
                {
                    var props = item.Key.Prop.Split(".", StringSplitOptions.RemoveEmptyEntries);

                    int i = 0;
                    foreach (var prop in props)
                    {
                        i++;
                        if (obj != null) obj = obj.GetType().GetProperty(prop)?.GetValue(obj, null);

                        //
                        if (i == props.Length)
                        {
                            if (obj != null)
                            {
                                var p = obj.GetType().GetProperty(item.Key.Prop)?.GetCustomAttribute(typeof(ExcelExporterAttribute));
                                if (p is ExcelExporterAttribute excelattr)
                                {
                                    //二次定位取值
                                    if (excelattr.ValueProperty.NotNull())
                                    {
                                        var value = SetOtherValue(obj, excelattr.ValueProperty);
                                        SetCellValue(range, item, value);
                                        return;
                                    }
                                    else if (excelattr.ValueMethod.NotNull())
                                    {
                                        var value = SetOtherValueForMethod(obj, excelattr.ValueMethod);
                                        SetCellValue(range, item, value);
                                        return;
                                    }
                                }
                            }
                        }
                    }
                    SetCellValue(range, item, obj);
                }
                else
                {
                    var p = obj.GetType().GetProperty(item.Key.Prop)?.GetCustomAttribute(typeof(ExcelExporterAttribute));
                    if (p is ExcelExporterAttribute excelattr)
                    {
                        //二次定位取值
                        if (excelattr.ValueProperty.NotNull())
                        {
                            var value = SetOtherValue(obj, excelattr.ValueProperty);
                            SetCellValue(range, item, value);
                            return;
                        }
                        else if (excelattr.ValueMethod.NotNull())
                        {
                            var value = SetOtherValueForMethod(obj, excelattr.ValueMethod);
                            SetCellValue(range, item, value);
                            return;
                        }
                        else
                        {
                            SetCellValue(range, item, obj.GetType().GetProperty(item.Key.Prop)?.GetValue(obj, null));
                        }
                    }
                    else SetCellValue(range, item, obj.GetType().GetProperty(item.Key.Prop)?.GetValue(obj, null));
                    return;
                }
            }

        }

        private void SetCellValue(ExcelRange range, KeyValuePair<Column, PropertyInfo?> item, object? value)
        {


            if (value != null)
            {
                var type = value.GetType();


                if (type == typeof(DateTime) || type == typeof(DateTime?))
                {
                    range.Value = value;
                    var format = "yyyy-m-d h:mm";

                    if (item.Value?.GetCustomAttribute(typeof(ExcelExporterAttribute)) is ExcelExporterAttribute att)
                    {
                        format = att.Format;
                    }
                    range.Style.Numberformat.Format = format;
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                else if (type == typeof(decimal) || type == typeof(decimal?)
                   || type == typeof(float) || type == typeof(float?)
                    || type == typeof(double) || type == typeof(double?))
                {
                    range.Value = value;

                    var format = "#,##0.00";//保留两位小数
                    if (item.Value?.GetCustomAttribute(typeof(ExcelExporterAttribute)) is ExcelExporterAttribute att)
                    {
                        format = att.Format;
                    }
                    range.Style.Numberformat.Format = format;
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }
                else if (type == typeof(int) || type == typeof(int?))
                {
                    range.Value = value;

                    var format = "#,##0";//数字
                    if (item.Value?.GetCustomAttribute(typeof(ExcelExporterAttribute)) is ExcelExporterAttribute att)
                    {
                        format = att.Format;
                    }
                    range.Style.Numberformat.Format = format;
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }
                else
                {
                    if (item.Value?.GetCustomAttribute(typeof(ExcelExporterAttribute)) is ExcelExporterAttribute att)
                    {
                        if (att != null && att.IsImg)
                        {
                            if (value != null && value?.ToString()?.Length > 0)
                            {

                                try
                                {
                                    using var stream = httpClient.GetAsync(value?.ToString()).Result.Content.ReadAsStreamAsync().Result;
                                    ExcelPicture excelPicture =
                                    worksheet.Drawings.AddPicture(Yitter.IdGenerator.YitIdHelper.NextId().ToString() + ".jpg", stream);
                                    excelPicture.SetPosition(range.EntireRow.EndRow - 1, 0, range.EntireColumn.EndColumn - 1, 0);

                                    //   excelPicture.SetSize(60, 20);
                                    excelPicture.SetSize(att.Width, att.Height);

                                }
                                catch
                                {

                                }


                            }

                        }
                        else if (att != null && att.Base64Img)
                        {
                            if (value != null && value?.ToString()?.Length > 0)
                            {

                                try
                                {


                                    var parts = value?.ToString()?.Split("base64,", StringSplitOptions.RemoveEmptyEntries);
                                    if (parts != null && parts.Length == 2)
                                    {
                                        using var stream = new MemoryStream(Convert.FromBase64String(parts[1]));
                                        ExcelPicture excelPicture =
                                        worksheet.Drawings.AddPicture(Yitter.IdGenerator.YitIdHelper.NextId().ToString() + ".jpg", stream);
                                        excelPicture.SetPosition(range.EntireRow.EndRow-1, 0, range.EntireColumn.EndColumn-1, 0);
                                        //   excelPicture.SetSize(60, 20);
                                        excelPicture.SetSize(att.Width, att.Height);
                                    }
                                }
                                catch
                                {

                                }


                            }

                        }
                        else if (att != null && att.IsMulImg)
                        {
                            if (value != null && value?.ToString()?.Length > 0)
                            {
                                var imgs = value.ToString()?.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                                if (imgs != null && imgs.Length > 0)
                                {
                                    foreach (var img in imgs)
                                    {

                                        try
                                        {
                                            using var stream = httpClient.GetAsync(img).Result.Content.ReadAsStreamAsync().Result;
                                            ExcelPicture excelPicture =
                                            worksheet.Drawings.AddPicture(Yitter.IdGenerator.YitIdHelper.NextId().ToString() + ".jpg", stream);
                                            excelPicture.SetPosition(range.EntireRow.EndRow - 1, 0, range.EntireColumn.EndColumn - 1, 0);
                                            //   excelPicture.SetSize(60, 20);
                                            excelPicture.SetSize(att.Width, att.Height);

                                        }
                                        catch
                                        {

                                        }

                                    }

                                }
                            }
                        }
                        else
                        {
                            range.Value = value;
                        }
                    }
                    else
                    {
                        range.Value = value;
                    }
                }
            }
        }

        /// <summary>
        /// 获取ValueProperty的值
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="propName"></param>
        private object? SetOtherValue(object? obj, string propName)
        {
            object? value = null;
            if (obj != null)
            {
                if (propName.IndexOf(".") != -1)
                {
                    var props = propName.Split(".", StringSplitOptions.RemoveEmptyEntries);

                    foreach (var prop in props)
                    {
                        obj = obj?.GetType().GetProperty(prop, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance)?.GetValue(obj, null);
                    }
                    value = obj;
                }
                else
                {
                    value = obj.GetType().GetProperty(propName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance)?.GetValue(obj, null);
                }
            }
            return value;
        }
        private object? SetOtherValueForMethod(object? obj, string methodName)
        {
            object? value = null;
            if (obj != null)
            {
                if (methodName.IndexOf(".") != -1)
                {
                    var props = methodName.Split(".", StringSplitOptions.RemoveEmptyEntries);

                    foreach (var prop in props)
                    {
                        obj = obj?.GetType().GetMethod(prop, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance)?.Invoke(obj, null);
                    }
                    value = obj;
                }
                else
                {
                    value = obj.GetType().GetMethod(methodName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance)?.Invoke(obj, null);
                }
            }
            return value;
        }

        /// <summary>
        /// 
        /// </summary>
        public void Dispose()
        {
            this.httpClient?.Dispose();
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="colNames">列的汉字名</param>
    /// <param name="row">列名所有的行</param>
    /// <returns>类的属性名</returns>
    private static Dictionary<string, int> GetProps<T>(List<string> colNames, int row)
    {
        Dictionary<string, int> dicts = new Dictionary<string, int>();

        var ps = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        List<(string, string)> names = new List<(string, string)>();
        foreach (var item in ps)
        {
            if (!item.CanWrite) //如果不能设置值
            {
                continue;
            }
            bool t = false;
            foreach (var att in item.GetCustomAttributes(true))
            {
                if (att is ExcelExporterAttribute bb)
                {
                    if (bb.Title.NotNull())
                    {
                        names.Add((bb.Title, item.Name));
                        t = true;
                        continue;
                    }

                }
                else if (att is DescriptionAttribute des)
                {
                    if (des.Description.NotNull())
                    {
                        names.Add((des.Description ?? "", item.Name));
                        t = true;
                        continue;
                    }
                }
                else if (att is DisplayAttribute dis)
                {
                    if (dis.Name.NotNull())
                    {
                        names.Add((dis.Name ?? "", item.Name));
                        t = true;
                        continue;
                    }
                }
            }
           /* if (!t)
            {
                var xname = item.GetXmlDocumentation();
                if (xname.NotNull())
                {
                    names.Add((xname, item.Name));
                }
            }*/
        }


        foreach (var item in names)
        {
            if (!AddCol(colNames, dicts, item))
            {
                throw new Exception($"第{row}行未找到列名[{item}],导入表格的第{row}行必须有列名：{string.Join(",", names)}");
            }
        }
        return dicts;

    }
    static bool AddCol(List<string> colNames, Dictionary<string, int> dicts, (string, string) title)
    {
        var i = colNames.IndexOf(title.Item1);
        if (i != -1)
        {
            if (!dicts.ContainsKey(title.Item1))
            {
                dicts.Add(title.Item2, i + 1);
                return true;
            }
        }
        return false;
    }

    #endregion
}